
IF EXISTS(SELECT name FROM sys.sysdatabases where name='StudentAttendanceChecking')
	begin
	use master
	print 'in use'
	drop database StudentAttendanceChecking
	end
ELSE
	begin
	print 'successful'
	create database StudentAttendanceChecking
	end
go
use StudentAttendanceChecking
go
-- PHAN VUNG TAO CAC BANG --
create table GroupUser
(
	GroupUserID int identity(1,1) not null, -- ma nhom
	GroupName nvarchar(50) not null, -- ten gorup (1 giao vien, 2 nhan vien, 3 quan tri)
	constraint PK_GroupUser primary key (GroupUserID)
)
create table Permission
(
	PermissionID int identity(1,1) not null,
	GroupUserID int not null,-- nhom can phan quyen
	Active varchar(100),-- ten doi tuong ma can tao nhom
	PermissionStatus bit default 1,-- mac dinh luon luon su dung
	constraint PK_Permission primary key (PermissionID),
	constraint FK_Permission_GroupUser foreign key (GroupUserID) references GroupUser(GroupUserID)
)
go
create table Users --giang vien hoac nhan vien
(
	UsersID int identity(1,1) not null,
	UsersName nvarchar(30) check(len(UsersName) <= 30 and len(UsersName) > 0) not null, --ten giang vien chu nhiem
	Departments nvarchar(100), -- phong ban
	Account nvarchar(50) not null,-- tai khoan dang nhap
	Passwords nvarchar(40) not null, -- mat khau truy cap,tu tao mat khau cho giang vien neu giao vien
	Active bit,-- an hien giang vien hay hoat dong khong hoat dong
	GroupUserID int not null, -- nhom tai khoan co 3 nhom teachers va staffs, admin
	constraint PK_Users primary key (UsersID),
	constraint FK_Faculty_GroupUser foreign key (GroupUserID) references GroupUser(GroupUserID)
)
go
create table Branch
(
	BranchID int identity(1,1) not null,-- ma dia diem
	BranchName nvarchar(50) not null, -- ten dia diem FAT1, FAT 2...
	constraint PK_Branch primary key (BranchID)
)
go
create table TrainingProgramme
(
	CodeTrainingName varchar(10) check(len(CodeTrainingName) <= 10 and len(CodeTrainingName) > 0) not null,-- ten chuong trinh dao tao
	Descriptions nvarchar(max),
	StatusTraining bit default 1,-- 0 la khong hoat dong 1 la hoat dong
	DefaultTraining tinyint default 0, -- so nam dao tao
	constraint PK_TrainingProgramme primary key (CodeTrainingName)
)
go
create table Class
(
	ClassID int identity(1,1) not null, -- ma lop
	ClassName varchar(6) check(len(ClassName)>0 and len(ClassName)<=6) not null, -- ten lop
	UsersID int not null, -- ma nguoi dung, co the la giao vien hoac nhan vien
	BranchID int, -- ma dia diem
	TimeSlot varchar(40) check(len(TimeSlot) <= 40 and len(TimeSlot) > 0),-- thoi gian hoc tu may gio den may gio
	StartDay datetime, -- ngay vao hoc
	EndDay datetime, -- ngay ket thuc
	ClassStatus bit,-- 0 la khong hoat dong 1 la dang hoat dong 2 la sap hoat dong
	constraint PK_Class primary key (ClassID),
	constraint FK_Class_BranchID foreign key (BranchID) references Branch(BranchID),
	constraint FK_Class_UsersID foreign key (UsersID) references Users(UsersID)
)
go
create table Student
(
	StudentID varchar(6) not null,-- ma sinh vien vd A04879
	Name nvarchar(50) check(len(Name) >0 and len(Name)<=50) not null,-- ten sinh vien
	CodeTrainingName varchar(10) check(len(CodeTrainingName) <= 10 and len(CodeTrainingName) > 0),-- ten chuong trinh dao tao
	CurrentClassID int, -- lop hien tai dang hoc
	Gender bit default 1,-- gioi tinh 0 nam 1 nu
	BirthDay datetime, -- ngay sinh
	Addresss nvarchar(150) check(len(Addresss) <= 150),-- dia chi
	Phone varchar(15),-- dien thoai
	Email varchar(100), -- hom thu
	StudentStatus int, -- trang thai sinh vien
	Descriptions nvarchar(max), -- mo ta sinh vien neu co
	constraint PK_Student primary key (StudentID), -- khoa chinh
	constraint FK_Student_ClassID foreign key (CurrentClassID) references Class(ClassID),
	constraint FK_Student_TrainingProgramme foreign key (CodeTrainingName) references TrainingProgramme(CodeTrainingName)
)
go 
create table Subjects
(
	SubjectCode varchar(10), -- ma mon hoc
	SubjectName varchar(40), -- ten mon hoc
	SubjectTotalW int, -- tong so tiet hoc ly thuyet
	SubjectTotalP int, -- tong so tiet hoc thuc hanh
	--WTest int default 1, -- ly thuyet
	--PTest int default 1, -- thuc hanh
	Sem tinyint default 0,-- bo mon nay thuoc hoc ky may (1,2,3)
	constraint PK_Subject_SubjectCode primary key (SubjectCode)
)
go
create table Attendants
(
	AttendantsID int identity(1,1) not null, -- ma diem danh
	UsersID int not null, -- giao vien diem danh truong hop nay chi co nhom 1 dang nhap moi duoc diem danh
	ClassID int not null,	-- lop diem danh
	SubjectCode varchar(10), -- diem danh mon
	AttendantsDate datetime default(getdate()), -- ngay diem danh
	constraint PK_Attendants primary key (AttendantsID),
	constraint FK_Attendants_ClassID foreign key (ClassID) references Class(ClassID),
	constraint FK_Attendants_UsersID foreign key (UsersID) references Users(UsersID),
	constraint FK_Attendants_SubjectCode foreign key (SubjectCode) references Subjects(SubjectCode)
)
go
create table AttendantsDetails
(
	AttendantsDetailsID int identity(1,1) not null, -- ma diem danh chi tiet
	AttendantsID int not null, -- ma diem danh
	StudentID varchar(6) not null, -- ma sinh vien
	--AttendantsType bit not null, -- 0 la ly thuyet, 1 la thuc hanh
	AttendantsStatusW int, --ly thuyet 0 nghi hoc, 1 nghi phep, 2 di hoc
	AttendantsStatusP int, --thuc hanh 0 nghi hoc, 1 nghi phep, 2 di hoc
	AttendantsDetailsDateTime datetime default(getdate()),--mac dinh lay ngay gio he thong luc tich diem danh
	Note nvarchar(max), -- ghi chu neu co
	constraint PK_AttendantsDetails primary key (AttendantsDetailsID),
	constraint FK_AttendantsDetails_StudentID foreign key (StudentID) references Student(StudentID),
	constraint FK_AttendantsDetails_AttendantsID foreign key (AttendantsID) references Attendants(AttendantsID)
)
go
create table StudentChangeClass
(
-- xep lop cho hoc sinh
	StudentChangeClassID int identity(1,1) not null, -- ma xep lop
	StudentID varchar(6) not null, -- ma sinh vien
	--ClassID int not null, -- ma lop
	ClassLastedID int not null, -- lop cu
	ClassMoveToID int not null, -- lop moi dich chuyen(truong hop nay lam mot hanh dong chuyen lop thi lop nay se khac lop cu. Nhung chu y sau khi chuyen lop thi phai chuyen lop o bang sinh vien de xem sinh vien nay dang hoc lop nao)
	Note nvarchar(max), -- ghi chu
	constraint PK_StudentChangeClass primary key (StudentChangeClassID),
	--constraint FK_StudentChangeClass_ClassID foreign key (ClassID) references Class(ClassID), --ON UPDATE CASCADE,
	constraint FK_StudentChangeClass_StudentID foreign key (StudentID) references Student(StudentID)
)
--PHAN INSER DATA
/*
	moi hoc sinh khi dang ky hoc thi se duoc phan vao mot lop va co cac mon hoc cua minh
*/
/*go
insert into Branch(BranchName) values ('FAT 1')
insert into Branch(BranchName) values ('FAT 2')
insert into Branch(BranchName) values ('FAT 3')
insert into Branch(BranchName) values ('FAT G')
go
insert into TrainingProgrammeDetails(TrainingProgramme,StatusTraining,TrainingProgramme) values ('HDSE',1,2)
insert into TrainingProgrammeDetails(TrainingProgramme,StatusTraining,TrainingProgramme) values ('DIM',1,1)
go
insert into Teacher(TeacherName,Active) values ('Nguyen Khac Nhat',1);
insert into Teacher(TeacherName,Active) values ('Nguyen Viet Khoa',1);
insert into Teacher(TeacherName,Active) values ('Hoang Huong Giang',1);
insert into Teacher(TeacherName,Active) values ('Nguyen Xuan Sinh',1);
insert into Teacher(TeacherName,Active) values ('Do Huy Hoang',1);
insert into Teacher(TeacherName,Active) values ('Nguyen Tuan',1);
insert into Teacher(TeacherName,Active) values ('Nguyen Thanh Tung',1);
go
insert into Class(ClassName,BranchID,TrainingProgramme,TeacherID,StartDay,EndDay,TimeSlot,ClassStatus) values ('C1010G',1,'HDSE',1,'22/10/2011','22/10/2013','7h30-11h30',1)
insert into Class(ClassName,BranchID,TrainingProgramme,TeacherID,StartDay,EndDay,TimeSlot,ClassStatus) values ('C1011G',1,'HDSE',3,'22/10/2011','22/10/2013','7h30-11h30',1)
insert into Class(ClassName,BranchID,TrainingProgramme,TeacherID,StartDay,EndDay,TimeSlot,ClassStatus) values ('C1009G',1,'HDSE',4,'22/10/2011','22/10/2013','7h30-11h30',1)
insert into Class(ClassName,BranchID,TrainingProgramme,TeacherID,StartDay,EndDay,TimeSlot,ClassStatus) values ('C0910G',1,'HDSE',2,'22/10/2010','22/10/2012','13h30-17h30',1)
insert into Class(ClassName,BranchID,TrainingProgramme,TeacherID,StartDay,EndDay,TimeSlot,ClassStatus) values ('C0908G',3,'HDSE',1,'22/10/2011','22/10/2013','7h30-11h30',1)
go
insert into Student(Name,Gender,BrithDay,Addresss,Phone,Email,CurrentClass,Descriptions,StudentStatus) values ('Doan Tuan Anh',)
-- PHAN TAO CAC PROC --
--tao proc kiem tra ten ton tai va add du lieu vao data
go
create proc Add_Branch
@BranchName nvarchar(50)
as
--check ton tai ten
begin
	if exists(select Branch.BranchName from Branch where Branch.BranchName = @BranchName)
		rollback transaction
	else
		insert into Branch(BranchName) values (@BranchName)
end*/
